/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.prashant.sqlduniya.sqlmanager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author prashant
 */
public final class DBInfoUtil {

    private static final String[] DB_TABLE_TYPES = {"TABLE"};
    private static final String[] DB_VIEW_TYPES = {"VIEW"};
    private static final String[] DB_MIXED_TYPES = {"TABLE", "VIEW"};
    private static final String COLUMN_NAME_TABLE_NAME = "TABLE_NAME";
    private static final String COLUMN_NAME_COLUMN_NAME = "COLUMN_NAME";
    private static final String COLUMN_NAME_DATA_TYPE = "DATA_TYPE";
    private static final String COLUMN_NAME_VIEW_NAME = "VIEW_NAME";
    private static final String COLUMN_NAME_TYPE_NAME = "TYPE_NAME";
    private static final String ORACLE_VIEWS =
            "select object_name from user_objects where object_type = 'VIEW'";
    private static final String ORACLE_TABLES =
            "select object_name from user_objects where object_type = 'TABLE'";
    private static final String ORACLE_TABLES_AND_VIEWS =
            "select object_name from user_objects where object_type = 'TABLE' "
            + "or object_type = 'VIEW'";

    public static List<String> getTables(Connection conn, String catalog) {
        List<String> list = new ArrayList<String>();
        try {
            ResultSet rs = null;
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getTables(catalog, null, null, new String[]{"TABLE"});

            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                list.add(tableName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBInfoUtil.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public static List<String> getSchemas(Connection conn) {
        List<String> list = new ArrayList<String>();
        try {
            ResultSet rs = null;
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getCatalogs();
            while (rs.next()) {
                String schemaName = rs.getString("TABLE_CAT");
                list.add(schemaName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBInfoUtil.class.getName()).log(Level.FINE, null, ex);
        }
        return list;
    }

    public static List<String> getSchemas(Connection conn, String catalog) {
        List<String> items = getSchemas(conn);
        //if specified catalog exists show only that else show all
        if (items.contains(catalog)) {
            items.clear();
            items.add(catalog);
        }
        return items;
    }

    public static SQLResult getTableData(Connection conn, TableBean table,
            int offset, int limit) throws SQLException {
        SQLResult sqlResult = new SQLResult();
        
        List<Column> cols = new ArrayList<Column>();
        table.setColumns(cols);
        sqlResult.setTable(table);

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + table.getCatalog() +"."+ table.getLabel() + " Limit " + offset + "," + limit);
        int colsize = rs.getMetaData().getColumnCount();
        for (int i = 1; i <= colsize; i++) {
            String label = rs.getMetaData().getColumnLabel(i);
            Column col = new Column();
            col.setLabel(label);
            cols.add(col);
        }
        List<List<String>> result = new ArrayList<List<String>>();
        while (rs.next()) {
            List<String> row = new ArrayList<String>();
            for (Column aCol : cols) {
                String value = rs.getString(aCol.getLabel());
                row.add(value);
            }
            result.add(row);
        }
        sqlResult.setResult(result);

        return sqlResult;
    }

    public static SQLResult getResultData(Connection conn, String query) throws SQLException {
        SQLResult sqlResult = new SQLResult();
        TableBean tableNode = new TableBean("Result",null);
        List<Column> cols = new ArrayList<Column>();
        tableNode.setColumns(cols);
        sqlResult.setTable(tableNode);


        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        int colsize = rs.getMetaData().getColumnCount();
        for (int i = 1; i <= colsize; i++) {
            String label = rs.getMetaData().getColumnLabel(i);
            Column col = new Column();
            col.setLabel(label);
            cols.add(col);
        }
        List<List<String>> result = new ArrayList<List<String>>();
        while (rs.next()) {
            List<String> row = new ArrayList<String>();
            for (Column aCol : cols) {
                String value = rs.getString(aCol.getLabel());
                row.add(value);
            }
            result.add(row);
        }
        sqlResult.setResult(result);

        return sqlResult;
    }
}